import os
import sys
import pymysql
import xlwt
import cv2
import numpy as np
from PIL import Image
from PyQt5.QtWidgets import *
from PyQt5.QtCore import Qt, QRegExp
from PyQt5.QtGui import QFont, QIcon, QRegExpValidator, QPalette, QPixmap

from gui_ui.user_ui import Ui_UserZone

class UserControl(Ui_UserZone, QWidget):
    def __init__(self):
        super(UserControl, self).__init__()
        self.setupUi(self)
        self.initUI()

    def initUI(self):
        self.windowNatrue()
        self.center()
        self.signalSetting()
        self.userPersonalInit()
        self.showUserImage()
        self.userLessons_tableFresh()
        self.freshSignLog_auto()

    def windowNatrue(self):
        self.setWindowTitle("智能考勤系统-用户")
        self.setWindowIcon(QIcon("./gui_image/gui_Icon.png"))
        self.setToolTip("武昌工学院签到系统")
        self.setWindowOpacity(0.95)
        pe = QPalette()
        self.setAutoFillBackground(True)
        pe.setColor(QPalette.Window, Qt.lightGray)
        self.setPalette(pe)

    def center(self):
        screen = QDesktopWidget().screenGeometry()
        size = self.geometry()
        newLeft = (screen.width() - size.width()) // 2
        newTop = (screen.height() - size.height()) // 2
        self.move(newLeft, newTop)

    def onTreeClicked(self):
        item = self.treeWidget.currentItem()
        index_top = self.treeWidget.indexOfTopLevelItem(item)
        self.stackedWidget.setCurrentIndex(index_top)

    def closeEvent(self, event):
        closeresult = QMessageBox.question(self, "关闭提示：", "您真的要退出吗？", 
                                         QMessageBox.Yes | QMessageBox.No,
                                         QMessageBox.No)
        if closeresult == QMessageBox.Yes:
            event.accept()
        else:
            event.ignore()

    def signalSetting(self):
        self.treeWidget.clicked.connect(self.onTreeClicked)
        self.updateButton.clicked.connect(self.collectUserFaceData)
        self.saveChange.clicked.connect(self.userPasswordChange)
        self.updateUserImage.clicked.connect(self.update2UserImage)
        self.deleteMyself.clicked.connect(self.deleteUserAccount)
        self.freshLessons.clicked.connect(self.userLessons_tableFreshButton)
        self.userLesson2Excel.clicked.connect(self.userLessons_save2ExcelButton)
        self.freshRecordButton.clicked.connect(self.freshSignLog_All)
        self.userSignLog2Excel.clicked.connect(self.userSignLog_save2ExcelButton)

    ########################## 个人信息页面 ###################################
    def userPersonalInit(self):
        self.userPasswordInit()
        self.Lcd2show_userClassSum()

    def Lcd2show_userClassSum(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from classtable where majorNumber = %s'
        rows = cursor.execute(sql, user_class)
        
        if rows:
            sql = 'select classNumber from classtable where majorNumber = %s'
            cursor.execute(sql, user_class)
            row = cursor.fetchone()
            row2 = ' '.join(map(str, row))
            
            self.classSum.setDigitCount(3)
            self.classSum.setSegmentStyle(QLCDNumber.Flat)
            self.classSum.setStyleSheet("border: 2px solid black; color: black; ")
            self.classSum.display(row2)
        else:
            QMessageBox.about(self, "发生错误", "未检索到该班级的信息！")

        cursor.close()
        conn.close()

    def collectUserFaceData(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        
        cam = cv2.VideoCapture(0 + cv2.CAP_DSHOW)
        cam.set(3, 640)
        cam.set(4, 480)
        face_detector = cv2.CascadeClassifier('./haarcascades/haarcascade_frontalface_default.xml')

        face_id = user_Id
        QMessageBox.about(self, "提示", "即将开始录入，请将正对摄像区域中央！")
        count = 0

        while True:
            ret, img = cam.read()
            gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY)
            faces = face_detector.detectMultiScale(gray, 1.3, 5)
            
            for (x, y, w, h) in faces:
                cv2.rectangle(img, (x, y), (x + w, y + h), (255, 0, 0), 2)
                count += 1
                path = "./userImg2train/" + str(user_class)
                folder = os.path.exists(path)
                if not folder:
                    os.makedirs(path)
                cv2.imwrite(path + "/" + str(face_id) + '_' + str(count) + ".jpg", gray[y:y + h, x:x + w])
                cv2.imshow('image', img)
            
            k = cv2.waitKey(20) & 0xff
            if k == 27 or count >= 30:
                break

        # 更新数据库标记
        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()
        
        sql = 'select * from information_schema.tables where table_name = %s'
        rows = cursor.execute(sql, user_class)
        
        if rows:
            sql = 'select * from `%s` where id_num = %s' % user_class
            row = cursor.execute(sql, user_Id)
            checked = "是"
            
            if row:
                sql = 'update `%s` set checked = %s where id_num = %s' % user_class
                cursor.execute(sql, (checked, user_Id))
                conn.commit()
        
        cursor.close()
        conn.close()
        
        cam.release()
        cv2.destroyAllWindows()
        self.trainUserClassData()

    def trainUserClassData(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        path = './userImg2train/' + user_class
        
        recognizer = cv2.face.LBPHFaceRecognizer_create()
        detector = cv2.CascadeClassifier("./haarcascades/haarcascade_frontalface_default.xml")

        allImg2List = os.listdir(path)
        allImg2List.sort(key=lambda x: int(x[:-4]))
        imagePaths = [os.path.join(path, f) for f in allImg2List]
        
        faceSamples = []
        ids = []

        for imagePath in imagePaths:
            PIL_img = Image.open(imagePath).convert('L')
            img_numpy = np.array(PIL_img, 'uint8')
            id = int(os.path.split(imagePath)[-1].split("_")[0])
            faces = detector.detectMultiScale(img_numpy)
            
            for (x, y, w, h) in faces:
                faceSamples.append(img_numpy[y:y + h, x:x + w])
                ids.append(id)

        QMessageBox.about(self, "提示", "正在同步到检测样本！请稍等！")
        print("\n [INFO] 正在重新训练样本数据。请稍候...")
        
        recognizer.train(faceSamples, np.array(ids))
        model2path = './uesrTrainer2save/' + user_class
        
        folder = os.path.exists(model2path)
        if not folder:
            os.makedirs(model2path)
        
        recognizer.write(model2path + '_trainer.yml')
        print("\n [INFO] 样本内用户数量: {0} 。 ".format(len(np.unique(ids))))
        QMessageBox.about(self, "提示", "同步检测样本成功！")

    def userPasswordInit(self):
        self.passswordReset1.setPlaceholderText('首字母大写，总长6到10位。')
        self.passswordReset1.setToolTip('首字母大写，6到10位数字字母。')
        self.passswordReset2.setPlaceholderText('请再次确认密码！')
        self.passswordReset2.setToolTip('请再次确认密码！')
        
        self.check_input()
        self.passswordReset1.textChanged.connect(self.check_input)
        self.passswordReset2.textChanged.connect(self.check_input)
        
        password_val = QRegExpValidator(QRegExp("^[A-Z][0-9A-Za-z]{10}$"))
        self.passswordReset1.setValidator(password_val)
        self.passswordReset2.setValidator(password_val)
        
        self.passswordReset1.setEchoMode(QLineEdit.PasswordEchoOnEdit)
        self.passswordReset2.setEchoMode(QLineEdit.Password)

    def check_input(self):
        if self.passswordReset1.text() and self.passswordReset2.text():
            self.saveChange.setEnabled(True)
        else:
            self.saveChange.setEnabled(False)

    def check_password(self):
        password_1 = self.passswordReset1.text()
        password_2 = self.passswordReset2.text()
        
        if len(password_1) < 6:
            QMessageBox.warning(self, '警告', '密码位数小于6')
            self.passswordReset1.setText('')
            self.passswordReset2.setText('')
        else:
            if password_1 != password_2:
                QMessageBox.warning(self, '警告', '两次密码输入结果不一致！')
                self.passswordReset1.setText('')
                self.passswordReset2.setText('')

    def userPasswordChange(self):
        self.check_password()
        
        if self.passswordReset1.text() and self.passswordReset2.text():
            user_password2Save = self.passswordReset1.text()
            user_Id = self.userId.text()
            user_class = user_Id[0:8]

            conn = pymysql.connect(
                host='47.93.16.211',
                port=3306,
                user='root',
                password='123456',
                db='mytest',
                charset='utf8'
            )
            
            cursor = conn.cursor()

            sql = 'select * from information_schema.tables where table_name = %s'
            rows = cursor.execute(sql, user_class)
            
            if rows:
                sql = 'select * from `%s` where id_num = %s' % user_class
                row = cursor.execute(sql, user_Id)
                
                if row:
                    sql = 'update `%s` set password = %s where id_num = %s' % user_class
                    cursor.execute(sql, (user_password2Save, user_Id))
                    conn.commit()
                    QMessageBox.about(self, "提示", "修改成功！")
                else:
                    QMessageBox.warning(self, '发生错误', '该用户不存在！')
            else:
                QMessageBox.warning(self, '发生错误', '该班级表单不存在！')

            cursor.close()
            conn.close()

    def showUserImage(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        path = "./userHeadImg/" + str(user_class)
        
        if os.path.exists(path + "/" + user_Id + '.jpg'):
            self.headImage.setAlignment(Qt.AlignCenter)
            self.headImage.setPixmap(QPixmap(path + "/" + user_Id + ".jpg"))
        else:
            self.headImage.setPixmap(QPixmap("./gui_image/gui_Icon.jpeg"))

    def update2UserImage(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        
        cam = cv2.VideoCapture(0 + cv2.CAP_DSHOW)
        cam.set(3, 640)
        cam.set(4, 480)
        face_detector = cv2.CascadeClassifier('./haarcascades/haarcascade_frontalface_default.xml')
        
        face_id = user_Id
        QMessageBox.about(self, "提示", "即将开始录入，请将正对摄像区域中央！")
        path = "./userHeadImg/" + str(user_class)
        endFlag = 0
        
        while True:
            ret, img = cam.read()
            faces = face_detector.detectMultiScale(img, 1.3, 5)
            
            for (x, y, w, h) in faces:
                cv2.rectangle(img, (x, y), (x + w, y + h), (255, 0, 0), 2)
                cv2.imshow('userImage', img)
                
                k = cv2.waitKey(20) & 0xff
                if k == 32:  # 空格键
                    folder = os.path.exists(path)
                    if not folder:
                        os.makedirs(path)
                    cv2.imwrite(path + "/" + str(face_id) + ".jpg", img[y - 65:y + h + 30, x:x + w + 20])
                    endFlag = 1
                    break
                elif k == 27:
                    break
            
            if endFlag:
                QMessageBox.about(self, "提示", "更新成功！")
                break
            
            cv2.imshow('userImage', img)
            k = cv2.waitKey(20) & 0xff
            if k == 27 or k == 32:
                break
        
        cam.release()
        cv2.destroyAllWindows()
        self.showUserImage()

    def deleteUserAccount(self):
        deleteUserAccount_result = QMessageBox.question(self, "不可恢复操作：", 
                                                       "请再次确认是否注销此账号？", 
                                                       QMessageBox.Yes | QMessageBox.No, 
                                                       QMessageBox.No)
        if deleteUserAccount_result == QMessageBox.Yes:
            user_Id = self.userId.text()
            user_class = user_Id[0:8]

            conn = pymysql.connect(
                host='47.93.16.211',
                port=3306,
                user='root',
                password='123456',
                db='mytest',
                charset='utf8'
            )
            
            cursor = conn.cursor()

            sql = 'select * from information_schema.tables where table_name = %s'
            rows = cursor.execute(sql, user_class)
            
            if rows:
                sql = 'select * from `%s` where id_num = %s' % user_class
                row = cursor.execute(sql, user_Id)
                
                if row:
                    sql_delete = 'delete from `%s` where id_num = %s' % user_class
                    cursor.execute(sql_delete, user_Id)
                    conn.commit()
                    QMessageBox.about(self, "提示", "注销成功！")
                    self.close()
                else:
                    QMessageBox.warning(self, '发生错误', '未找到该用户的表单！')
            else:
                QMessageBox.warning(self, '发生错误', '未找到该班级的表单！')

            cursor.close()
            conn.close()

    ########################## 班级课表页面 ###################################
    def userLessons_tableFresh(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from lessontable where toClass = %s'
        cursor.execute(sql, user_class)
        rows = cursor.fetchall()
        row = cursor.rowcount
        vol = len(rows[0]) if rows else 0

        cursor.close()
        conn.close()

        self.tableWidget.setRowCount(row)
        self.tableWidget.setColumnCount(vol)

        self.tableWidget.setHorizontalHeaderLabels(['班级编号', '课程名称', '开课日期', '结课日期','一周课表','上课时间','下课时间'])
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.tableWidget.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        for i in range(row):
            for j in range(vol):
                temp_data = rows[i][j]
                data = QTableWidgetItem(str(temp_data))
                self.tableWidget.setItem(i, j, data)

    def userLessons_tableFreshButton(self):
        self.userLessons_tableFresh()
        QMessageBox.about(self, "提示", "刷新成功！")

    def userLessons_save2ExcelButton(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        save2path = './save2Excel/' + user_class + '_lessontable.xls'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from lessontable where toClass = %s'
        cursor.execute(sql, user_class)
        results = cursor.fetchall()
        fields = cursor.description

        workbook = xlwt.Workbook()
        sheet_name = user_class + '_lessontable'
        sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)

        for field in range(0, len(fields)):
            sheet.write(0, field, fields[field][0])

        for row in range(1, len(results) + 1):
            for col in range(0, len(fields)):
                sheet.write(row, col, u'%s' % results[row - 1][col])

        workbook.save(save2path)
        QMessageBox.about(self, "提示", "导出成功！")

        cursor.close()
        conn.close()

    ########################## 签到日志页面 ###################################
    def freshSignLog_auto(self):
        self.freshSignRecord_table()
        self.freshlateRecord_table()
        self.freshCountSum()

    def freshSignLog_All(self):
        self.freshSignLog_auto()
        QMessageBox.about(self, '提示', '刷新成功！')

    def freshSignRecord_table(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        signUpSheetName = str(user_class) + "_signdata"
        successSignState = '1'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = "select * from %s where (userId = %s) and (signState = %s)" % (signUpSheetName, '%s', '%s')
        cursor.execute(sql, (user_Id, successSignState))
        rows = cursor.fetchall()
        rowsum = cursor.rowcount

        if rowsum == 0:
            cursor.close()
            conn.close()
            self.signRecord.setRowCount(0)
            return

        vol = 4

        cursor.close()
        conn.close()

        lessonName_list = []
        location_list = []
        date_list = []
        time_list = []

        for i in range(rowsum):
            lessonName_list.append(str(rows[i][2]))
            location_list.append(str(rows[i][3]))
            date_list.append(str(rows[i][4]))
            time_list.append(str(rows[i][5]))

        form_4XN_Matrix = np.matrix([lessonName_list, location_list, date_list, time_list])
        form_NX4_Matrix = np.transpose(form_4XN_Matrix)
        form_NX4_Array = np.array(form_NX4_Matrix)

        self.signRecord.setRowCount(rowsum)
        self.signRecord.setColumnCount(vol)

        self.signRecord.setHorizontalHeaderLabels(['课程名称', '签到地点', '签到日期', '签到时间'])
        self.signRecord.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.signRecord.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.signRecord.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.signRecord.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        for i in range(rowsum):
            for j in range(vol):
                temp_data = form_NX4_Array[i][j]
                data = QTableWidgetItem(str(temp_data))
                self.signRecord.setItem(i, j, data)

    def freshlateRecord_table(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        signUpSheetName = str(user_class) + "_signdata"
        lateSignState = '2'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = "select * from %s where (userId = %s) and (signState = %s)" % (signUpSheetName, '%s', '%s')
        cursor.execute(sql, (user_Id, lateSignState))
        rows = cursor.fetchall()
        rowsum = cursor.rowcount

        if rowsum == 0:
            cursor.close()
            conn.close()
            self.lateRecord.setRowCount(0)
            return

        vol = 4

        cursor.close()
        conn.close()

        lessonName_list = []
        location_list = []
        date_list = []
        time_list = []

        for i in range(rowsum):
            lessonName_list.append(str(rows[i][2]))
            location_list.append(str(rows[i][3]))
            date_list.append(str(rows[i][4]))
            time_list.append(str(rows[i][5]))

        form_4XN_Matrix = np.matrix([lessonName_list, location_list, date_list, time_list])
        form_NX4_Matrix = np.transpose(form_4XN_Matrix)
        form_NX4_Array = np.array(form_NX4_Matrix)

        self.lateRecord.setRowCount(rowsum)
        self.lateRecord.setColumnCount(vol)

        self.lateRecord.setHorizontalHeaderLabels(['课程名称', '签到地点', '签到日期', '签到时间'])
        self.lateRecord.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.lateRecord.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.lateRecord.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.lateRecord.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        for i in range(rowsum):
            for j in range(vol):
                temp_data = form_NX4_Array[i][j]
                data = QTableWidgetItem(str(temp_data))
                self.lateRecord.setItem(i, j, data)

    def freshCountSum(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        signUpSheetName = str(user_class) + "_signdata"

        # 出勤次数
        successSignState = '1'
        sign_count = self.getSignCount(signUpSheetName, user_Id, successSignState)
        self.signCountSum.setStyleSheet("border: 2px solid black; color: black; ")
        self.signCountSum.display(sign_count)

        # 迟到次数
        lateSignState = '2'
        late_count = self.getSignCount(signUpSheetName, user_Id, lateSignState)
        self.lateCountSum.setStyleSheet("border: 2px solid black; color: black; ")
        self.lateCountSum.display(late_count)

        # 请假次数
        freeSignState = '3'
        free_count = self.getSignCount(signUpSheetName, user_Id, freeSignState)
        self.freeCountSum.setStyleSheet("border: 2px solid black; color: black; ")
        self.freeCountSum.display(free_count)

    def getSignCount(self, signUpSheetName, user_Id, state):
        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = "select * from %s where (userId = %s) and (signState = %s)" % (signUpSheetName, '%s', '%s')
        cursor.execute(sql, (user_Id, state))
        rows = cursor.fetchall()
        rowsum = cursor.rowcount

        if rowsum == 0:
            cursor.close()
            conn.close()
            return 0

        lessonName_list = []
        date_list = []
        lessonName_date_list = []

        for i in range(rowsum):
            lessonName_list.append(str(rows[i][2]))
            date_list.append(str(rows[i][4]))

        for i in range(rowsum):
            lessonName_date_list.append(lessonName_list[i] + date_list[i])

        lessonName_date_set = set(lessonName_date_list)
        count = len(lessonName_date_set)

        cursor.close()
        conn.close()
        return count

    def userSignLog_save2ExcelButton(self):
        user_Id = self.userId.text()
        user_class = user_Id[0:8]
        signUpSheetName = str(user_class) + "_signdata"
        save2path = './save2Excel/' + signUpSheetName + '.xls'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from information_schema.tables where table_name = %s'
        rows = cursor.execute(sql, signUpSheetName)
        
        if not rows:
            QMessageBox.about(self, '提示', "该班级的签到表单不存在！")
            return

        sql = 'select * from %s where userId = %s' % (signUpSheetName, '%s')
        cursor.execute(sql, user_Id)
        results = cursor.fetchall()
        fields = cursor.description

        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet(signUpSheetName, cell_overwrite_ok=True)

        for field in range(0, len(fields)):
            sheet.write(0, field, fields[field][0])

        for row in range(1, len(results) + 1):
            for col in range(0, len(fields)):
                sheet.write(row, col, u'%s' % results[row - 1][col])

        workbook.save(save2path)
        QMessageBox.about(self, '提示', "导出成功！")

        cursor.close()
        conn.close()